/*
 * # 应用所有数据库表:
 *  Calendar - 日历
 * 
 * # 描述:
 *  向所有用户显示日历，并允许系统管理人员管理本组织内部使用的节假日和工作日，这是在业务中工作日计数的主要依据。
 * 
 * Copyright (c), 上海沃恩信息科技有限公司.
 * All rights reserved.
 * Licensed under BSD (https://www.pittypat.work/bsd.html).
 */

use "pittypat_app";
go

-- 确保架构存在
if schema_id('waln_calendar') is null
	exec('create schema "waln_calendar" authorization "dbo"');
go

/*
 * # 辅助函数:
 *  _pittypat_table_exists - 检查指定的表是否已经存在
 * 
 * # 描述:
 *  检查指定的表是否已经存在。
 */

if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_calendar"."_pittypat_table_exists"') and "type" in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	exec('create function "waln_calendar"."_pittypat_table_exists"() returns int begin return 0; end');
go

alter function "waln_calendar"."_pittypat_table_exists"(@tbl varchar(30))
returns bit
as
begin
	if @tbl is not null and object_id(N'"waln_calendar".' + @tbl, N'U') is not null
		return 1;
	return 0;
end;
go

/*
 * # 辅助函数:
 *  _pittypat_column_exists - 检查指定的列是否已经存在
 * 
 * # 描述:
 *  检查指定的列是否已经存在。
 */

if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_calendar"."_pittypat_column_exists"') and "type" in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	exec('create function "waln_calendar"."_pittypat_column_exists"() returns int begin return 0; end');
go

alter function "waln_calendar"."_pittypat_column_exists"(@tbl varchar(30), @col varchar(30))
returns bit
as
begin
	if @tbl is not null and @col is not null and 
		exists (select * from "sys"."columns" where "object_id" = object_id(N'[waln_calendar].' + @tbl, N'U') and "name" = @col)
		return 1;
	return 0;
end;
go

/*
 * # 辅助函数:
 *  _pittypat_index_exists - 检查指定的索引是否已经存在
 * 
 * # 描述:
 *  检查指定的索引是否已经存在。
 */

if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_calendar"."_pittypat_index_exists"') and "type" in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	exec('create function "waln_calendar"."_pittypat_index_exists"() returns int begin return 0; end');
go

alter function "waln_calendar"."_pittypat_index_exists"(@tbl varchar(30), @idx varchar(63))
returns bit
as
begin
	if @tbl is not null and @idx is not null and 
		exists (select * from "sys"."indexes" where "object_id" = object_id(N'"waln_calendar".' + @tbl, N'U') and "name" = @idx)
		return 1;
	return 0;
end;
go

/*
 * # 数据库表 - Sql Server:
 *  Dates - 日期
 * 
 * # 描述:
 *  表示一个已经安排的工作日或节假日。没有安排的日期将按照默认规则计算为工作日或节假日：周一到周五的为工作日，周六和周日的日期为节假日。
 */

if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_calendar"."_pittypat_replace_table"') and "type" in (N'P', N'PC'))
	exec('create procedure "waln_calendar"."_pittypat_replace_table" as begin set nocount on; end');
go

alter procedure "waln_calendar"."_pittypat_replace_table"
as
begin
	set nocount on;

	if "waln_calendar"."_pittypat_table_exists"('"Dates"') = 0
	begin
		create table "waln_calendar"."Dates"
		(
			"Value" date not null, -- 日期值
			"IsWorkday" bit null, -- 是否工作日
			"Festivals" nvarchar(50) null -- 节日名称
		)
		on "primary";

		-- 索引 K_Dates
		alter table "waln_calendar"."Dates" add constraint
		"K_Dates" primary key clustered
		(
			"Value" asc
		)
		with
		(
			statistics_norecompute = off,
			ignore_dup_key = off,
			allow_row_locks = on,
			allow_page_locks = on
		)
		on "primary";
	end
	else
	begin
		-- 列 Value 
		if "waln_calendar"."_pittypat_column_exists"('Dates', 'Value') = 0
			alter table "waln_calendar"."Dates" add
			"Value" date not null;
		else
			alter table "waln_calendar"."Dates" alter column
			"Value" date not null;

		-- 列 IsWorkday 
		if "waln_calendar"."_pittypat_column_exists"('Dates', 'IsWorkday') = 0
			alter table "waln_calendar"."Dates" add
			"IsWorkday" bit null;
		else
			alter table "waln_calendar"."Dates" alter column
			"IsWorkday" bit null;

		-- 列 Festivals 
		if "waln_calendar"."_pittypat_column_exists"('Dates', 'Festivals') = 0
			alter table "waln_calendar"."Dates" add
			"Festivals" nvarchar(50) null;
		else
			alter table "waln_calendar"."Dates" alter column
			"Festivals" nvarchar(50) null;
	end
end
go

exec "waln_calendar"."_pittypat_replace_table";
go

/*
	select
		"Value", -- 日期值
		"IsWorkday", -- 是否工作日
		"Festivals"  -- 节日名称
	from
		"waln_calendar"."Dates"
	where
		"col_where" = @col_where;

	insert into "waln_calendar"."Dates"
	(
		"Value", -- 日期值
		"IsWorkday", -- 是否工作日
		"Festivals"  -- 节日名称
	)
	values
	(
		@value, -- 日期值
		@isWorkday, -- 是否工作日
		@festivals  -- 节日名称
	);

	update
		"waln_calendar"."Dates"
	set
		"Value" = @value, -- 日期值
		"IsWorkday" = @isWorkday, -- 是否工作日
		"Festivals" = @festivals  -- 节日名称
	where
		"col_where" = @col_where;

	delete from
		"waln_calendar"."Dates"
	where
		"col_where" = @col_where;
*/
